/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import DTO.*;
import UTIL.*;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
/**
 *
 * @author Huong Giang
 */
public class KhachHangDAL {
    public static boolean insertKH(KhachHang emp) {
         boolean result = true;
        try {
            Connection connect = SqlDataAccessHelper.getConnection();
            CallableStatement cs = connect.prepareCall("{call sp_InsertKH(?, ?, ?, ?, ?,?)}");
            cs.setInt("cmt", emp.getCmt());
            cs.setString("ten", emp.getTen());
            cs.setInt("ma",emp.getMaPhong());
            cs.setString("tt", emp.getKieuTT());
            cs.setDate("in", emp.getNgayThue());
            cs.setDate("out", emp.getNgayTra());
            int n = cs.executeUpdate();
            if (n == 0) {
                result =false;
            }
            
            connect.close();
        } catch (SQLException ex) {
        }        
        return result;

    }
 
        
    //Updating

    public static boolean updateKH(KhachHang emp) {
        boolean result = true;
        try {
            Connection connect = SqlDataAccessHelper.getConnection();
            CallableStatement cs = connect.prepareCall("{call sp_UpdateKH(?, ?, ?, ?, ?,?)}");
            cs.setString("ten", emp.getTen());
            cs.setInt("ma", emp.getMaPhong());
            cs.setString("tt", emp.getKieuTT());
            cs.setDate("in", emp.getNgayThue());
            cs.setDate("out", emp.getNgayTra());
            int n = cs.executeUpdate();
            if (n == 0) {
                result = false;
            }
            connect.close();
        } catch (SQLException ex) {
        }
        return result;

    }
    //Retrieving  

    public static ArrayList<KhachHang> selectKHall() {
    ArrayList<KhachHang> list = new ArrayList<KhachHang>();
        try {
            Connection connect = SqlDataAccessHelper.getConnection();
            CallableStatement cs = connect.prepareCall("{call sp_SelectKHall1()}");
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                KhachHang emp = new KhachHang();
                emp.setCmt(rs.getInt("CMT"));
                emp.setTen(rs.getString("Ten"));
                emp.setMaPhong(rs.getInt("MaPhong"));
                emp.setGia(rs.getInt("Gia"));
                emp.setKieuTT(rs.getString("KieuTT"));
                emp.setNgayThue(rs.getDate("NgayThue"));
                emp.setNgayTra(rs.getDate("NgayTra"));
                list.add(emp);
            }
            connect.close();
        } catch (SQLException ex) {
        }
        return list;
    }

    public static KhachHang selectKHByID(int CMT) {
        KhachHang emp = new KhachHang();
        try {
            Connection connect = SqlDataAccessHelper.getConnection();
            CallableStatement cs = connect.prepareCall("{call sp_selectKHByID(?)}");
            cs.setInt("CMT", CMT);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                emp.setCmt(rs.getInt("CMT"));
                emp.setTen(rs.getString("Ten"));
                emp.setMaPhong(rs.getInt("MaPhong"));
                emp.setKieuTT(rs.getString("KieuTT"));
                emp.setNgayThue(rs.getDate("NgayThue"));
                emp.setNgayTra(rs.getDate("NgayTra"));
            }
            connect.close();
        } catch (SQLException ex) {
        }
        return emp;
    }
 public static ArrayList<KhachHang> searchKHByCMT(int CMT){
        ArrayList<KhachHang> list = new ArrayList<KhachHang>();
        try {
            Connection connect = SqlDataAccessHelper.getConnection();
            CallableStatement cs = connect.prepareCall("{call sp_selectKHByID(?)}");
            cs.setInt("cmt", CMT);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                KhachHang emp = new KhachHang();
                emp.setCmt(rs.getInt("CMT"));
                emp.setTen(rs.getString("Ten"));
                emp.setMaPhong(rs.getInt("MaPhong"));
                emp.setKieuTT(rs.getString("KieuTT"));
                emp.setNgayThue(rs.getDate("NgayThue"));
                emp.setNgayTra(rs.getDate("NgayTra"));
                list.add(emp);
            }
            connect.close();
        } catch (SQLException ex) {
        }
        return list;
    }
   public static boolean checkKHByCMT(int CMT) {
        boolean result = false;
        try {
            Connection connect = SqlDataAccessHelper.getConnection();
            CallableStatement cs = connect.prepareCall("{call sp_selectKH(?)}");
            cs.setInt("CMT", CMT);
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                result = true;
            }
            connect.close();
        } catch (SQLException ex) {
        }
        return result;
    }

   public static boolean deleteKH(int cmt) {
       boolean result = true;
        try {
            Connection connect = SqlDataAccessHelper.getConnection();
            CallableStatement cs = connect.prepareCall("{call sp_DeleteKH(?)}");
            cs.setInt("CMT", cmt);
            int n = cs.executeUpdate();
            if (n == 0) {
                result = false;
            }
            connect.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }
   
   
    public static boolean deleteKH(String cmt) throws SQLException {
         boolean result = true;
         try {
           Connection connect = SqlDataAccessHelper.getConnection();
            CallableStatement cs = connect.prepareCall("{call sp_DeleteKH(?)}");
            cs.setString("CMT", cmt );
            int n = cs.executeUpdate();
            if (n == 0) {
                result = false;
            }
            connect.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
            
    }
    }


